package com.dao.impl;

import com.bean.*;
import com.dao.SellDao;
import com.util.JDBC_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class SellDaoImpl implements SellDao {

    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement ps = null;

    @Override
    public Sell sellInit(User user, Customer customer,String address) {
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String nowDate = simpleDateFormat.format(date) + "";
        int userId = user.getId();
        int customerId = customer.getId();
        int SellId = 0;
        Sell sell = new Sell();

        try {
            if (haveNotOutSellExist(user, customer, address)) {
                conn = JDBC_Util.getConnection();
                String sql1 = "select id from t_sell where is_out_warehouse = 0 and user_id = ? and customer_id = ? and address = ?";
                ps = conn.prepareStatement(sql1);
                ps.setInt(1, userId);
                ps.setInt(2, customerId);
                ps.setString(3, address);
                rs = ps.executeQuery();
                if (rs.next()) {
                    SellId = rs.getInt("id");
                }

            } else {
                String sql = "insert into t_sell values (null," + userId + ",'" + nowDate + "'," + customerId + ",0,'"+address+"',0)";
                conn = JDBC_Util.getConnection();
                ps = conn.prepareStatement(sql);
                ps.executeUpdate();


                String sql1 = "select max(id) from t_sell where is_out_warehouse = 0 and user_id = ? and customer_id = ?";
                ps = conn.prepareStatement(sql1);
                ps.setInt(1, userId);
                ps.setInt(2, customerId);
                rs = ps.executeQuery();
                if (rs.next()) {
                    SellId = rs.getInt("max(id)");
                }
            }

            sell.setUser(user);
            sell.setSellDate(nowDate);
            sell.setCustomer(customer);
            sell.setId(SellId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return sell;
    }

    @Override
    public List<SellInfo> getAllGoods(Sell sell,String address) {
        conn = JDBC_Util.getConnection();
        List<SellInfo> sellInfos = new ArrayList<>();
        Customer customer = sell.getCustomer();
        int cusId = customer.getId();
        int userid = sell.getUser().getId();
        int sellId = 0;
        try {
            String sql1 = "select max(id) from t_sell where customer_id = ? and is_out_warehouse = 0 and address = ? and user_id = ?";
            ps = conn.prepareStatement(sql1);
            ps.setInt(1,cusId);
            ps.setString(2,address);
            ps.setInt(3,userid);
            rs = ps.executeQuery();
            if (rs.next()) {
                sellId = rs.getInt("max(id)");
            }

            String sql = "select t_sell_info.id,goods_id,goods_name,manufactor,buy_price,sell_price,number from t_sell_info" +
                " inner join t_goods tg on t_sell_info.goods_id = tg.id" +
                " inner join t_sell t on t_sell_info.sell_id = t.id" +
                " inner join t_customer tc on t.customer_id = tc.id" +
                " where t.id = ? and tc.id = ? and t.address = ?";


            ps = conn.prepareStatement(sql);
            ps.setInt(1, sellId);
            ps.setInt(2, cusId);
            ps.setString(3,address);
            rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                int goodsId = rs.getInt("goods_id");
                String goodsName = rs.getString("goods_name");
                String manufactor = rs.getString("manufactor");
                int buyPrice = rs.getInt("buy_price");
                int sellPrice = rs.getInt("sell_price");
                int number = rs.getInt("number");

                Goods goods = new Goods();
                goods.setId(goodsId);
                goods.setName(goodsName);
                goods.setManufactor(manufactor);
                goods.setBuyPrice(buyPrice);
                goods.setSellPrice(sellPrice);
                SellInfo sellInfo = new SellInfo();
                sellInfo.setGoods(goods);
                sellInfo.setSell(sell);
                sellInfo.setNumber(number);
                sellInfo.setId(id);

                sellInfos.add(sellInfo);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return sellInfos;
    }

    @Override
    public int addGoods(int key, int number, Sell sell) {
        int n = 0;
        int stockNumber = 0;
        int goodsId = 0;
        try {
            conn = JDBC_Util.getConnection();
            String sql = "select number,goods_id from t_stock where id = " + key + "";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                stockNumber = rs.getInt("number");
                goodsId = rs.getInt("goods_id");
            }
            System.out.println(stockNumber);
            System.out.println(number);
            if (stockNumber < number) {
                n = -1;
            } else {
                int sellId = sell.getId();
                String sql3 = "insert into t_sell_info values (null," + goodsId + "," + number + "," + sellId +")";
                n = JDBC_Util.update(sql3);

                String sql1 = "update t_stock set number = number - " + number + " where goods_id = " + goodsId + "";
                n += JDBC_Util.update(sql1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return n;
    }

    @Override
    public int deleteGoods(int key) {
        int m = 0;
        int oldNum = 0;
        int stockNumber = 0;
        int goodsId = 0;
        conn = JDBC_Util.getConnection();
        String sql = "select number as num,goods_id from t_sell_info where id = " + key + "";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                oldNum = rs.getInt("num");
                goodsId = rs.getInt("goods_id");
            }

            String sql2 = "update t_stock set number = number + "+oldNum+" where goods_id = "+goodsId+"" ;
            int n = JDBC_Util.update(sql2);
            String sql3 = "delete from t_sell_info where id = " + key + "";
            m = JDBC_Util.update(sql3);


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }



        return m;
    }

    @Override
    public int updateGoodsNumber(int key, int number) {
        int n = 0;
        int oldNum = 0;
        int stockNumber = 0;
        int goodsId = 0;
        conn = JDBC_Util.getConnection();
        String sql = "select number as num,goods_id from t_sell_info where id = " + key + "";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                oldNum = rs.getInt("num");
                goodsId = rs.getInt("goods_id");
            }

            String sql1 = "select number as num from t_stock where goods_id = " + goodsId + "";
            ps = conn.prepareStatement(sql1);
            rs = ps.executeQuery();
            if (rs.next()) {
                stockNumber = rs.getInt("num");
            }
            System.out.println(oldNum);
            int smone = number - oldNum;
            if(smone > stockNumber){
                n = -1;
            }else {
                String sql2 = "update t_sell_info set number = " + number + " where id = " + key + "";
                n = JDBC_Util.update(sql2);

                String sql4 = "update t_stock set number = number - " + smone + " where goods_id = " + goodsId + "";
                n += JDBC_Util.update(sql4);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return n;
    }

    @Override
    public int outWarehouse(List<SellInfo> sellInfos,int money) {
        conn = JDBC_Util.getConnection();
        int sellID = 0;
        int m = 0;
        for (SellInfo sellInfo : sellInfos) {
            sellID = sellInfo.getSell().getId();
            int number = sellInfo.getNumber();
            Goods goods = sellInfo.getGoods();
            int goodsId = goods.getId();
        }
        String sql3 = "update t_sell set is_out_warehouse = 1,money = "+money+"  where id =  " + sellID + " ";
        m = JDBC_Util.update(sql3);
        return m;
    }

    @Override
    public boolean haveNotOutSellExist(User user, Customer customer,String address) {
        int customerId = customer.getId();
        int id = user.getId();
        String sql = "select count(0) from t_sell where user_id = ?" +
                " and is_out_warehouse = 0 and customer_id = ? and address = ?";
        conn = JDBC_Util.getConnection();
        boolean flag = false;
        int i = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            ps.setInt(2, customerId);
            ps.setString(3,address);
            rs = ps.executeQuery();
            if (rs.next()) {
                i = rs.getInt("count(0)");
            }
            if (i > 0) {
                flag = true;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return flag;
    }

    @Override
    public List<Sell> getAllSell() {
        List<Sell> sells = new ArrayList<>();
        conn = JDBC_Util.getConnection();
        String sql = "select t_sell.id, tu.real_name, sell_date, ts.customer_name,t_sell.money from t_sell" +
                " inner join t_users tu on t_sell.user_id = tu.id" +
                " inner join t_customer ts on t_sell.customer_id = ts.id" +
                " where is_out_warehouse = 1";

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String realName = rs.getString("real_name");
                String sellDate = rs.getString("sell_date");
                String customerName = rs.getString("customer_name");
                int money = rs.getInt("money");

                Customer customer = new Customer();
                customer.setName(customerName);
                User user = new User();
                user.setRealName(realName);
                Sell sell = new Sell();
                sell.setUser(user);
                sell.setCustomer(customer);
                sell.setSellDate(sellDate);
                sell.setId(id);
                sell.setMoney(money);

                sells.add(sell);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return sells;
    }

    @Override
    public Sell getSelectSell(int key) {
        Sell sell = new Sell();
        conn = JDBC_Util.getConnection();

        String sql = "select ts.id as ts_id,t_sell.id, tu.real_name, sell_date, ts.customer_name,t_sell.address from t_sell" +
                " inner join t_users tu on t_sell.user_id = tu.id" +
                " inner join t_customer ts on t_sell.customer_id = ts.id" +
                " where t_sell.id = " + key + "";


        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                int customerId = rs.getInt("ts_id");
                String sellDate = rs.getString("sell_date");
                String realName = rs.getString("real_name");
                String customerName = rs.getString("customer_name");
                String addrss = rs.getString("address");

                Customer customer = new Customer();
                customer.setName(customerName);
                customer.setId(customerId);
                User user = new User();
                user.setRealName(realName);
                sell.setId(key);
                sell.setCustomer(customer);
                sell.setUser(user);
                sell.setSellDate(sellDate);
                sell.setAddress(addrss);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return sell;
    }

    @Override
    public List<SellInfo> getSellInfo(Sell sell) {
        Customer customer = sell.getCustomer();
        int cusId = customer.getId();
        int sellId = sell.getId();
        List<SellInfo> sellInfos = new ArrayList<>();
        System.out.println(cusId);
        System.out.println(sellId);
        conn = JDBC_Util.getConnection();

        String sql = "select t_sell_info.id,goods_id,goods_name,manufactor,buy_price,sell_price,number from t_sell_info" +
                " inner join t_goods tg on t_sell_info.goods_id = tg.id" +
                " inner join t_sell t on t_sell_info.sell_id = t.id" +
                " inner join t_customer tc on t.customer_id = tc.id" +
                " where tc.id = ? and t.id = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, cusId);
            ps.setInt(2, sellId);
            rs = ps.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                int goodsId = rs.getInt("goods_id");
                String goodsName = rs.getString("goods_name");
                String manufactor = rs.getString("manufactor");
                int buyPrice = rs.getInt("buy_price");
                int sellPrice = rs.getInt("sell_price");
                int number = rs.getInt("number");

                Goods goods = new Goods();
                goods.setId(goodsId);
                goods.setName(goodsName);
                goods.setManufactor(manufactor);
                goods.setBuyPrice(buyPrice);
                goods.setSellPrice(sellPrice);
                SellInfo sellInfo = new SellInfo();
                sellInfo.setGoods(goods);
                sellInfo.setSell(sell);
                sellInfo.setNumber(number);
                sellInfo.setId(id);

                sellInfos.add(sellInfo);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return sellInfos;
    }

    @Override
    public int getMoney(User user, Customer customer, String address) {
        int money = 0 ;
        int customerId = customer.getId();
        int userId = user.getId();
        conn = JDBC_Util.getConnection();
        String sql = "select tg.sell_price,t_sell_info.number from t_sell_info" +
                " inner join t_goods tg on t_sell_info.goods_id = tg.id" +
                " inner join t_sell ts on t_sell_info.sell_id = ts.id" +
                " inner join t_customer tc on ts.customer_id = tc.id" +
                " where user_id = ? and customer_id = ? and is_out_warehouse = 0 and ts.address = ?";

        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,userId);
            ps.setInt(2,customerId);
            ps.setString(3,address);
            rs = ps.executeQuery();
            while (rs.next()) {
                int sellPrice = rs.getInt("sell_price");
                int number = rs.getInt("number");
                int newMoney = sellPrice * number;
                money += newMoney;
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return money;
    }

    public List<Sell> getFindSell(String custoerName,String startDate,String endDate){
        List<Sell> list = new ArrayList<>();
        conn = JDBC_Util.getConnection();

        String sql = "select t_sell.id, tu.real_name, sell_date, ts.customer_name,t_sell.money from t_sell" +
                " inner join t_users tu on t_sell.user_id = tu.id" +
                " inner join t_customer ts on t_sell.customer_id = ts.id" +
                " where is_out_warehouse = 1";

        if(!custoerName.equals("")  && custoerName != null){
            sql += " and customer_name like '%"+custoerName+"%'";
        }

        if(!startDate.equals("")  && startDate != null){
            sql += " and sell_date > '"+startDate+"'";
        }

        if(!endDate.equals("")  && endDate != null){
            sql += " and sell_date < '"+endDate+"'";
        }

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String realName = rs.getString("real_name");
                String sellDate = rs.getString("sell_date");
                String customerName = rs.getString("customer_name");
                int money = rs.getInt("money");

                Customer customer = new Customer();
                customer.setName(customerName);
                User user = new User();
                user.setRealName(realName);
                Sell sell = new Sell();
                sell.setUser(user);
                sell.setCustomer(customer);
                sell.setSellDate(sellDate);
                sell.setId(id);
                sell.setMoney(money);

                list.add(sell);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC_Util.close(rs, conn, ps);
        }
        return list;
    };
}
